# -*- coding:utf-8 -*-

from alldata.models import c_company_jane
from tools.w_decorator import performance
from alldata.tools.w_other import Mysql

import configparser


class obtain_user_shop(object):
    def __init__(self):
        self.config_file = r'conf\config.ini'
        self.config = configparser.ConfigParser()
        self.config.read(self.config_file, encoding="utf-8")

    def mysql_con(self):
        # host = self.config.get('mysql', 'host')
        # port = self.config.get('mysql', 'port')
        # dbname = self.config.get('mysql', 'dbname')
        # user = self.config.get('mysql', 'username')
        # pwd = self.config.get('mysql', 'passwd')
        # db = pymysql.connect(host, user, pwd, dbname, charset='utf8')
        # cursor = db.cursor()
        mysqlcon = Mysql()
        company_contrast, company_data, company_list = self.company(mysqlcon)

        self.shop_list(mysqlcon, company_contrast, company_data, company_list)
        self.user_list(mysqlcon, company_contrast, company_data, company_list)
        # cursor.close()

    # def company(self):
    #     jane = self.config.options('company')
    #     global company_data
    #     global company_list
    #     company_contrast = {}
    #     company_list = []
    #     company_data = {}
    #     for i in jane:
    #         company_contrast[self.config.get('company', i)] = i
    #         company_list.append(i)
    #         company_data[i] = []
    #     # print(company_data, company_list, company_contrast)
    #     return company_contrast

    @performance
    def company(self, c_cursor):
        global company_data
        global company_list
        company_contrast = {}
        company_list = []
        company_data = {}
        company_id = {}
        full_name_sql = "select companyId, companyName from y_info_company where isUsing = 1 and isDelete = 0"
        c_jane_list = c_company_jane.objects.values_list('full_name', 'jane_name')
        c_full_list = c_cursor.getAll(full_name_sql)
        for c_i in c_jane_list:
            company_data[c_i[1]] = []
            company_list.append(c_i[1])
            company_contrast[[c_n[1] for c_n in c_full_list if c_n[1] == c_i[0]][0]] = c_i[1]
        return company_contrast, company_data, company_list


    def shop_list(self, cursor, s_json, company_data, company_list):
        shop_sql_list = ['shop_d_sql', 'shop_d_inc_sql', 'shop_s_sql', 'shop_s_inc_sql', 'shop_no_sql']
        shop_d_sql = "SELECT a.companyId, ( SELECT companyName FROM y_info_company b WHERE a.companyId = b.companyId ) AS companyName, a.num FROM (SELECT companyId, COUNT(1) AS num FROM `s_shop` WHERE isUsing = 1 AND shopState = 1 AND isDelete=0 AND DATE_FORMAT(createDate, '%Y%m%d') <= " + new_datetime + " GROUP BY companyId ) a"
        shop_d_inc_sql = "SELECT a.companyId, ( SELECT companyName FROM y_info_company b WHERE a.companyId = b.companyId ) AS companyName, a.num FROM (SELECT companyId, COUNT(1) AS num FROM `s_shop` WHERE isUsing = 1 AND shopState = 1 AND isDelete = 0 AND DATE_FORMAT(createDate, '%Y%m%d') >= " + old_datetime + " AND DATE_FORMAT(createDate, '%Y%m%d') <= " + new_datetime + " GROUP BY companyId ) a"
        shop_s_sql = "SELECT d.companyId, e.companyName, SUM(CASE WHEN onLineState = 0 AND (deviceStyle = 3 OR deviceStyle = 4) THEN 1 ELSE 0 END) AS onLineCount FROM s_shop_device c LEFT JOIN s_shop d ON c.shopId = d.shopId LEFT JOIN y_info_company e ON e.companyId = d.companyId WHERE c.isDelete = 0 AND DATE_FORMAT(c.createDate, '%Y%m%d') <= " + new_datetime + " GROUP BY d.companyId"
        shop_s_inc_sql = "SELECT d.companyId, e.companyName, SUM(CASE WHEN onLineState = 0 AND (deviceStyle = 3 OR deviceStyle = 4) THEN 1 ELSE 0 END) AS onLineCount FROM s_shop_device c LEFT JOIN s_shop d ON c.shopId = d.shopId LEFT JOIN y_info_company e ON e.companyId = d.companyId WHERE c.isDelete = 0 AND DATE_FORMAT(c.createDate, '%Y%m%d') >= " + old_datetime + " AND DATE_FORMAT(c.createDate, '%Y%m%d') <= " + new_datetime + " GROUP BY d.companyId"
        shop_no_sql = "SELECT companyId , ( SELECT companyName FROM y_info_company c WHERE c.companyId = b.companyId ) AS companyName, COUNT(1) FROM s_shop b WHERE isDelete = 0 AND isUsing = 1 AND NOT EXISTS ( SELECT 1 FROM c_account_shop a WHERE a.shopId = b.shopId AND a.isdelete = 0 ) GROUP BY companyId"
        n = 0
        for i in shop_sql_list:
            n += 1
            one_sql = eval(i)
            one_data = cursor.getAll(one_sql)
            try:
                for i in one_data:
                    if i[1] in s_json.keys():
                        company_data[s_json[i[1]]].append(int(i[2]))
            except:
                print('缺失数据')
            for k, v in company_data.items():
                if len(v) < n:
                    company_data[k].append(0)

    def user_list(self, cursor, u_json, company_data, company_list):
        user_sql_list = ["user_e_sql", "user_e_inc_sql", "user_zx_sql", "user_zx_inc_sql", "user_h_sql",
                         "user_h_inc_sql", "user_zg_sql", "user_zg_inc_sql"]
        user_e_sql = "SELECT tt.companyId, ( SELECT cc.companyName FROM y_info_company cc WHERE cc.isUsing=1 AND cc.isDelete=0 AND  cc.companyId = tt.companyId ) AS companyName, tt.num FROM (SELECT t.companyId, COUNT(1) AS num FROM y_info_account a, (SELECT c.companyId, b.accountId FROM s_shop c, c_account_shop b WHERE c.shopId = b.shopId AND c.isDelete = 0 AND c.shopState = 1 AND c.isUsing = 1 AND b.isDelete = 0 AND DATE_FORMAT(b.createDate, '%Y%m%d') <= " + new_datetime + " ) t WHERE t.accountId = a.accountId AND a.isDelete = 0 GROUP BY t.companyId ) tt"
        user_e_inc_sql = "SELECT tt.companyId, ( SELECT cc.companyName FROM y_info_company cc WHERE cc.isUsing=1 AND cc.isDelete=0 AND  cc.companyId = tt.companyId ) AS companyName, tt.num FROM (SELECT t.companyId, COUNT(1) AS num FROM y_info_account a, (SELECT c.companyId, b.accountId FROM s_shop c, c_account_shop b WHERE c.shopId = b.shopId AND c.isDelete = 0 AND c.shopState = 1 AND c.isUsing = 1 AND b.isDelete = 0 AND DATE_FORMAT(b.createDate, '%Y%m%d') >= " + old_datetime + " AND DATE_FORMAT(b.createDate, '%Y%m%d') <= " + new_datetime + " ) t WHERE t.accountId = a.accountId AND a.isDelete = 0 GROUP BY t.companyId ) tt"
        user_zx_sql = "SELECT t.companyId, ( SELECT e.companyName FROM y_info_company e WHERE e.isUsing=1 AND e.isDelete=0 AND e.companyId = t.companyId ) AS companyName, t.num FROM (SELECT c.companyId, COUNT(1) AS num FROM c_info_employee c, (SELECT a.employeeId FROM c_info_employee_role a, c_roler_function b WHERE a.roleId = b.rolerId AND b.functionId = 2 AND a.isDelete = 0 AND b.isDelete = 0 AND DATE_FORMAT(b.createDate, '%Y%m%d') <= " + new_datetime + " ) d WHERE c.employeeId = d.employeeId AND c.isDelete = 0 GROUP BY c.companyId ) t"
        user_zx_inc_sql = "SELECT t.companyId, ( SELECT e.companyName FROM y_info_company e WHERE e.isUsing=1 AND e.isDelete=0 AND e.companyId = t.companyId ) AS companyName, t.num FROM (SELECT c.companyId, COUNT(1) AS num FROM c_info_employee c, (SELECT a.employeeId FROM c_info_employee_role a, c_roler_function b WHERE a.roleId = b.rolerId AND b.functionId = 2 AND a.isDelete = 0 AND b.isDelete = 0 AND DATE_FORMAT(b.createDate, '%Y%m%d') >= " + old_datetime + " AND DATE_FORMAT(b.createDate, '%Y%m%d') <= " + new_datetime + " ) d WHERE c.employeeId = d.employeeId AND c.isDelete = 0 GROUP BY c.companyId ) t"
        user_h_sql = "SELECT t.companyId, ( SELECT e.companyName FROM y_info_company e WHERE e.isUsing=1 AND e.isDelete=0 AND e.companyId = t.companyId ) AS companyName, t.num FROM (SELECT c.companyId, COUNT(1) AS num FROM c_info_employee c, (SELECT a.employeeId FROM c_info_employee_role a, c_roler_function b WHERE a.roleId = b.rolerId AND b.functionId = 5 AND DATE_FORMAT(b.createDate, '%Y%m%d') <= " + new_datetime + " AND a.isDelete = 0 AND b.isDelete = 0 ) d WHERE c.employeeId = d.employeeId AND c.isDelete = 0 GROUP BY c.companyId ) t"
        user_h_inc_sql = "SELECT t.companyId, ( SELECT e.companyName FROM y_info_company e WHERE e.isUsing=1 AND e.isDelete=0 AND e.companyId = t.companyId ) AS companyName, t.num FROM (SELECT c.companyId, COUNT(1) AS num FROM c_info_employee c, (SELECT a.employeeId FROM c_info_employee_role a, c_roler_function b WHERE a.roleId = b.rolerId AND b.functionId = 5 AND DATE_FORMAT(b.createDate, '%Y%m%d') >= " + old_datetime + " AND DATE_FORMAT(b.createDate, '%Y%m%d') <= " + new_datetime + " AND a.isDelete = 0 AND b.isDelete = 0 ) d WHERE c.employeeId = d.employeeId AND c.isDelete = 0 GROUP BY c.companyId ) t"
        user_zg_sql = "SELECT t.companyId, ( SELECT e.companyName FROM y_info_company e WHERE e.isUsing=1 AND e.isDelete=0 AND e.companyId = t.companyId ) AS companyName, t.num FROM (SELECT c.companyId, COUNT(1) AS num FROM c_info_employee c, (SELECT a.employeeId FROM c_info_employee_role a, c_roler_function b WHERE a.roleId = b.rolerId AND b.functionId = 3 AND DATE_FORMAT(b.createDate, '%Y%m%d') <= " + new_datetime + " AND a.isDelete = 0 AND b.isDelete = 0 ) d WHERE c.employeeId = d.employeeId AND c.isDelete = 0 GROUP BY c.companyId ) t"
        user_zg_inc_sql = "SELECT t.companyId, ( SELECT e.companyName FROM y_info_company e WHERE e.isUsing=1 AND e.isDelete=0 AND e.companyId = t.companyId ) AS companyName, t.num FROM (SELECT c.companyId, COUNT(1) AS num FROM c_info_employee c, (SELECT a.employeeId FROM c_info_employee_role a, c_roler_function b WHERE a.roleId = b.rolerId AND b.functionId = 3 AND DATE_FORMAT(b.createDate, '%Y%m%d') >= " + old_datetime + " AND DATE_FORMAT(b.createDate, '%Y%m%d') <= " + new_datetime + " AND a.isDelete = 0 AND b.isDelete = 0 ) d WHERE c.employeeId = d.employeeId AND c.isDelete = 0 GROUP BY c.companyId ) t"
        n = 5
        for i in user_sql_list:
            one_sql = eval(i)
            one_data = cursor.getAll(one_sql)
            try:
                n += 1
                for i in one_data:
                    if i[1] in u_json.keys():
                        company_data[u_json[i[1]]].append(i[2])
                for k, v in company_data.items():
                    if len(v) < n:
                        company_data[k].append(0)
            except:
                print('缺失数据')

    def modify_data(self, old_date, new_date):
        global old_datetime
        global new_datetime
        old_datetime = old_date
        new_datetime = new_date
        self.mysql_con()
        md_all_list = []
        md_json_name = [ 'shop_dr', 'shop_dr_a', 'shop_on', 'shop_on_a', 'shop_no_sql', 'user_ed', 'user_ed_a', 'user_zx', 'user_zx_a', 'user_hw', 'user_hw_a', 'user_zg', 'user_zg_a', 'shop_name']
        for md_i in company_list:
            md_one_json = {}
            md_one_json[md_json_name[13]] = md_i
            md_one_data = company_data[md_i]
            for md_n in range(len(md_one_data)):
                md_one_json[md_json_name[md_n]] = md_one_data[md_n]
            md_all_list.append(md_one_json)
        # print("================================")
        # print(md_all_list)
        # print(company_data)
        # print("==============================")
        print(md_all_list)
        print(company_data)
        print(company_list)
        return md_all_list, company_data, company_list
